﻿#region Copyright (c) 2006-2012 nHydrate.org, All Rights Reserved
// --------------------------------------------------------------------- *
//                           NHYDRATE.ORG                                *
//              Copyright (c) 2006-2012 All Rights reserved              *
//                                                                       *
//                                                                       *
// This file and its contents are protected by United States and         *
// International copyright laws.  Unauthorized reproduction and/or       *
// distribution of all or any portion of the code contained herein       *
// is strictly prohibited and will result in severe civil and criminal   *
// penalties.  Any violations of this copyright will be prosecuted       *
// to the fullest extent possible under law.                             *
//                                                                       *
// THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
// TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
// TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
// CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
// THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF THE NHYDRATE GROUP *
//                                                                       *
// UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
// PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
// SUBSTANTIALLY THE SAME, FUNCTIONALITY AS THIS PRODUCT                 *
//                                                                       *
// THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
// CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF NHYDRATE,          *
// THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO             *
// INSURE ITS CONFIDENTIALITY.                                           *
//                                                                       *
// THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
// PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
// EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
// THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
// SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                       *
// THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
// --------------------------------------------------------------------- *
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;

namespace nHydrate.DataImport.SqlClient
{
	public class SchemaModelHelper : ISchemaModelHelper
	{
		#region Public Methods
		
		public bool IsValidConnectionString(string connectionString)
		{
			var valid = false;
			var conn = new System.Data.SqlClient.SqlConnection();
			try
			{
				conn.ConnectionString = connectionString;
				conn.Open();
				valid = true;
			}
			catch (Exception ex)
			{
				valid = false;
			}
			finally
			{
				conn.Close();
			}
			return valid;
		}

		public bool IsSupportedSQLVersion(string connectionString)
		{
			var ds = DatabaseHelper.ExecuteDataset(connectionString, "SELECT SERVERPROPERTY('productversion')");
			var version = (string)ds.Tables[0].Rows[0][0];
			if (version.StartsWith("10."))
				return true;
			else if (version.StartsWith("9."))
				return true;
			else
				return false;
		}

		public SQLServerTypeConstants GetSQLVersion(string connectionString)
		{
			var ds = DatabaseHelper.ExecuteDataset(connectionString, "SELECT SERVERPROPERTY('productversion')");
			var version = (string)ds.Tables[0].Rows[0][0];
			if (version.StartsWith("10."))
			{
				var ds2 = DatabaseHelper.ExecuteDataset(connectionString, "SELECT SERVERPROPERTY('Edition')");
				var version2 = (string)ds2.Tables[0].Rows[0][0];
				if (version2 == "SQL Azure")
					return SQLServerTypeConstants.SQLAzure;
				else
					return SQLServerTypeConstants.SQL2008;
			}
			else
			{
				return SQLServerTypeConstants.SQL2005;
			}
		}
		
		#endregion

		internal static string GetSqlDatabaseTables()
		{
			var sb = new StringBuilder();
			sb.AppendLine("DECLARE @bar varchar(150)");
			sb.AppendLine("DECLARE @val varchar(150)");
			sb.AppendLine("DECLARE @tab table");
			sb.AppendLine("(");
			sb.AppendLine("xName varchar(150) NOT NULL,");
			sb.AppendLine("xValue varchar(150) NULL,");
			sb.AppendLine("xSchema varchar(150) NOT NULL");
			sb.AppendLine(")");
			sb.AppendLine("INSERT INTO @tab SELECT so.name, null, sc.name [schema] FROM sys.tables so INNER JOIN sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.name <> 'dtproperties' AND (so.name <> 'sysdiagrams') AND (so.name <> '__nhydrateschema')AND (so.name <> '__nhydrateobjects') AND NOT (so.name like '__AUDIT__%')");
			sb.AppendLine("select xName as name, xSchema as [schema], xValue selectionCriteria from @tab WHERE xName <> 'dtproperties' ORDER BY xName");
			return sb.ToString();
		}

		internal static string GetSqlColumnsForTable()
		{
			return GetSqlColumnsForTable(null);
		}

		internal static string GetSqlForUniqueConstraints()
		{
			var sb = new StringBuilder();
			sb.AppendLine("select o.name as TableName, col.name as ColumnName");
			sb.AppendLine("from sys.indexes i inner join sys.objects o on i.object_id = o.object_id ");
			sb.AppendLine("	inner join sys.index_columns ic on i.index_id = ic.index_id and ic.object_id = o.object_id");
			sb.AppendLine("	INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id");
			sb.AppendLine("where i.is_unique = 1 and is_primary_key = 0 and is_unique_constraint = 1");
			return sb.ToString();
		}

		internal static string GetSqlForIndexes()
		{
			var sb = new StringBuilder();
			sb.AppendLine();
			sb.AppendLine("SELECT ");
			sb.AppendLine("     ind.name as indexname");
			sb.AppendLine("     ,ind.is_primary_key");
			sb.AppendLine("    --,ind.index_id ");
			sb.AppendLine("    --,ic.index_column_id ");
			sb.AppendLine("    ,t.name as tablename");
			sb.AppendLine("    ,col.name as columnname");
			sb.AppendLine("    ,ic.is_descending_key");
			sb.AppendLine("    ,ic.key_ordinal");
			sb.AppendLine("    ,ind.type_desc");
			sb.AppendLine("    ,ind.is_unique_constraint");
			sb.AppendLine("    ,ind.is_primary_key");
			sb.AppendLine("    --,ic.* ");
			sb.AppendLine("    --,col.* ");
			sb.AppendLine("    --,ind.* ");
			sb.AppendLine("FROM sys.indexes ind ");
			sb.AppendLine("INNER JOIN sys.index_columns ic ");
			sb.AppendLine("    ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id ");
			sb.AppendLine("INNER JOIN sys.columns col ");
			sb.AppendLine("    ON ic.object_id = col.object_id and ic.column_id = col.column_id ");
			sb.AppendLine("INNER JOIN sys.tables t ");
			sb.AppendLine("    ON ind.object_id = t.object_id ");
			sb.AppendLine("WHERE (1=1) ");
			//sb.AppendLine("    AND ind.is_primary_key = 0 ");
			//sb.AppendLine("    AND ind.is_unique = 0 ");
			//sb.AppendLine("    AND ind.is_unique_constraint = 0 ");
			sb.AppendLine("    AND t.is_ms_shipped = 0 ");
			sb.AppendLine("    AND ic.key_ordinal <> 0");
			sb.AppendLine("ORDER BY ");
			sb.AppendLine("    ind.name, ic.key_ordinal");
			return sb.ToString();
		}

		internal static string GetSqlColumnsForComputed()
		{
			var sb = new StringBuilder();
			sb.AppendLine("select o.name as tablename, c.name as columnname, c.definition");
			sb.AppendLine("from sys.computed_columns c inner join sys.objects o on c.object_id = o.object_id");
			return sb.ToString();
		}

		internal static string GetSqlColumnsForTable(string tableName)
		{
			var sb = new StringBuilder();
			sb.AppendLine("SELECT");
			sb.AppendLine(" 	c.ORDINAL_POSITION as colorder,");
			sb.AppendLine(" 	c.TABLE_NAME as tablename,");
			sb.AppendLine(" 	c.COLUMN_NAME as columnname,");
			sb.AppendLine("(");
			sb.AppendLine("select top 1 c1.name");
			sb.AppendLine("from sys.indexes i");
			sb.AppendLine("join sysobjects o ON i.object_id = o.id");
			sb.AppendLine("join sysobjects pk ON i.name = pk.name");
			sb.AppendLine("AND pk.parent_obj = i.object_id");
			sb.AppendLine("AND pk.xtype = 'PK'");
			sb.AppendLine("join sys.index_columns ik on i.object_id = ik.object_id");
			sb.AppendLine("and i.index_id = ik.index_id");
			sb.AppendLine("join syscolumns c1 ON ik.object_id = c1.id");
			sb.AppendLine("AND ik.column_id = c1.colid");
			sb.AppendLine("AND c1.name = c.COLUMN_NAME");
			sb.AppendLine("where o.name = c.TABLE_NAME");
			sb.AppendLine(") as [isPrimaryKey],");
			sb.AppendLine(" 	case WHEN");
			sb.AppendLine(" 	(");
			sb.AppendLine(" 		SELECT ");
			sb.AppendLine(" 				count(*) ");
			sb.AppendLine(" 			FROM ");
			sb.AppendLine(" 				INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE foreignkeyccu");
			sb.AppendLine(" 				INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS foreignkeytc on foreignkeyccu.CONSTRAINT_NAME = foreignkeytc.CONSTRAINT_NAME AND");
			sb.AppendLine(" 																												foreignkeyccu.CONSTRAINT_SCHEMA = foreignkeytc.CONSTRAINT_SCHEMA AND");
			sb.AppendLine(" 																												foreignkeytc.CONSTRAINT_TYPE = 'FOREIGN KEY'");
			sb.AppendLine(" 			WHERE");
			sb.AppendLine(" 				foreignkeyccu.TABLE_SCHEMA = c.TABLE_SCHEMA AND");
			sb.AppendLine(" 				foreignkeyccu.TABLE_NAME = c.TABLE_NAME AND");
			sb.AppendLine(" 				foreignkeyccu.COLUMN_NAME = c.COLUMN_NAME ");
			sb.AppendLine(" 	) > 0 THEN 'true' ELSE 'false' END as isForeignKey,");
			sb.AppendLine(" 	c.DATA_TYPE as datatype,");
			sb.AppendLine(" 	s.xtype,");
			sb.AppendLine(" 	c.numeric_precision AS [precision], c.numeric_scale AS [scale],");
			sb.AppendLine(" 		case when	c.CHARACTER_MAXIMUM_LENGTH is null or c.CHARACTER_MAXIMUM_LENGTH > 8000 then s.length else c.CHARACTER_MAXIMUM_LENGTH end as length,");
			sb.AppendLine(" 	case when c.IS_NULLABLE = 'No' then 'false' else 'true' end as allowNull, ");
			sb.AppendLine(" 	case when c.COLUMN_DEFAULT is null then '' else c.COLUMN_DEFAULT end as defaultValue,");
			sb.AppendLine(" 	case when COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 then 'true' else 'false' end as isIdentity,");
			sb.AppendLine(" 	c.COLLATION_NAME AS collation");
			sb.AppendLine(" FROM ");
			sb.AppendLine(" 	INFORMATION_SCHEMA.COLUMNS c ");
			sb.AppendLine(" 	INNER JOIN systypes s on s.name = c.DATA_TYPE");
			if (!string.IsNullOrEmpty(tableName))
				sb.AppendLine(" WHERE c.TABLE_NAME = '" + tableName + "'");
			sb.AppendLine(" ORDER BY");
			sb.AppendLine(" 	c.TABLE_NAME,");
			sb.AppendLine(" 	c.ORDINAL_POSITION");
			return sb.ToString();
		}

		internal static string GetSqlForRelationships()
		{
			var sb = new StringBuilder();
			sb.AppendLine("SELECT ");
			sb.AppendLine("	 KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'");
			sb.AppendLine("	, KCU1.TABLE_NAME AS 'FK_TABLE_NAME'");
			sb.AppendLine("	, KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME' ");
			sb.AppendLine("	, KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'");
			sb.AppendLine("	, KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'");
			sb.AppendLine("	, so.id");
			sb.AppendLine("FROM ");
			sb.AppendLine("	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC");
			sb.AppendLine("	JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1");
			sb.AppendLine("		ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG ");
			sb.AppendLine("		AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA");
			sb.AppendLine("		AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME");
			sb.AppendLine("JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2");
			sb.AppendLine("	ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG ");
			sb.AppendLine("		AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA");
			sb.AppendLine("		AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME");
			sb.AppendLine("		AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION");
			sb.AppendLine("JOIN sysobjects so");
			sb.AppendLine("	ON KCU1.CONSTRAINT_NAME = so.name");
			sb.AppendLine("WHERE");
			sb.AppendLine("	so.xtype = 'F'");
			sb.AppendLine("ORDER BY");
			sb.AppendLine("	KCU1.CONSTRAINT_NAME,");
			sb.AppendLine("	KCU1.ORDINAL_POSITION");
			return sb.ToString();
		}

		internal static string GetSqlIndexesForTable()
		{
			var sb = new StringBuilder();

			sb.AppendLine("select t.name as tablename, i.name as indexname, c.name as columnname, i.is_primary_key");
			sb.AppendLine("from sys.tables t");
			sb.AppendLine("inner join sys.indexes i on i.object_id = t.object_id");
			sb.AppendLine("inner join sys.index_columns ic on ic.object_id = t.object_id");
			sb.AppendLine("inner join sys.columns c on c.object_id = t.object_id and");
			sb.AppendLine("ic.column_id = c.column_id");

			//sb.AppendLine("select o.name as tablename, i.name as indexname, i.is_primary_key from sys.objects o inner join sys.indexes i on o.object_id = i.object_id where o.[type] = 'U'");
			return sb.ToString();
		}

		internal static string GetSqlForViews()
		{
			var sb = new StringBuilder();
			sb.AppendLine("select s.name as schemaname, v.name, m.definition from sys.views v inner join sys.sql_modules m on v.object_id = m.object_id inner join sys.schemas s on s.schema_id= v.schema_id");
			return sb.ToString();
		}

		internal static string GetViewBody(string sql)
		{
			var regEx = new Regex(@"CREATE VIEW[\r\n\s]*[a-zA-Z0-9\[\]_\.]*[\r\n\s]*AS[\r\n\s]*([\s\S\r\n]*)", RegexOptions.IgnoreCase);
			var match = regEx.Match(sql);
			if (match != null && match.Groups != null && match.Groups.Count == 2)
				sql = match.Groups[1].Value;
			else
			{
				sql = sql.Replace("\r", string.Empty);
				var arr = sql.Split('\n').ToList();
				var sb = new StringBuilder();

				var inBody = false;
				foreach (var lineText in arr)
				{
					//This is FAR from perfect. It assumes the creation line ends with the "AS" keyword for a stored proc
					if (inBody)
					{
						sb.AppendLine(lineText);
					}
					else if (!inBody && (lineText.ToLower().Trim().EndsWith(" as") || lineText.ToLower().Trim() == "as"))
					{
						inBody = true;
					}
				}
				sql = sb.ToString();

			}

			return sql.Trim();
		}

		internal static string GetSqlForViewsColumns()
		{
			var sb = new StringBuilder();
			sb.AppendLine("select v.name as viewname, c.name as columnname, c.system_type_id, c.max_length, c.precision, c.scale, c.is_nullable from sys.views v inner join sys.columns c on v.object_id = c.object_id order by v.name, c.name");
			return sb.ToString();
		}

		internal static string GetSqlForFunctions()
		{
			var sb = new StringBuilder();
			sb.AppendLine("select o.*, s.name as schemaname from sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id WHERE [type] IN ('FN', 'IF', 'TF') and o.name <> 'fn_diagramobjects'");
			return sb.ToString();
		}

		internal static string GetFunctionBody(string schema, string name, string connectionString)
		{
			var sb = new StringBuilder();
			var ds = DatabaseHelper.ExecuteDataset(connectionString, "sp_helptext '[" + schema + "].[" + name + "]'");
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				var t = (string)dr["Text"] + string.Empty;
				sb.AppendLine(t.Replace("\r\n", string.Empty).Replace("\r", string.Empty).Replace("\n", string.Empty));
			}

			var sql = sb.ToString();
			var regEx = new Regex(@"CREATE\s*FUNCTION[\r\n\s]*[a-zA-Z0-9\[\]_\.]*.*RETURNS.*AS[\r\n\s]+(RETURN[\s\S\r\n]*)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
			var match = regEx.Match(sql);
			if (match != null && match.Groups != null && match.Groups.Count == 2)
			{
				sql = match.Groups[1].Value;
			}
			else
			{
				regEx = new Regex(@"CREATE\s*FUNCTION[\r\n\s]*[a-zA-Z0-9\[\]_\.]*.*RETURNS.*(BEGIN[\s\S\r\n]*)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
				match = regEx.Match(sql);
				if (match != null && match.Groups != null && match.Groups.Count == 2)
				{
					sql = match.Groups[1].Value;
				}
				else
				{
					regEx = new Regex(@"CREATE\s*FUNCTION[\r\n\s]*[a-zA-Z0-9\[\]_\.]*.*RETURNS.*AS[\r\n\s]+([\s\S\r\n]*)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
					match = regEx.Match(sql);
					if (match != null && match.Groups != null && match.Groups.Count == 2)
						sql = match.Groups[1].Value;
					else
						System.Diagnostics.Debug.Write(string.Empty);
				}
			}

			return sql.Trim();
		}

		internal static string GetSqlForStoredProceduresParameters()
		{
			var sb = new StringBuilder();
			sb.AppendLine("SELECT	dbo.syscolumns.xtype, dbo.sysobjects.name, dbo.sysobjects.id,");
			sb.AppendLine("		dbo.syscolumns.name AS ColName,");
			sb.AppendLine("		dbo.systypes.name AS ColType,");
			sb.AppendLine("		dbo.syscolumns.isnullable,");
			sb.AppendLine("		dbo.syscolumns.length,");
			sb.AppendLine("		dbo.syscolumns.isoutparam");
			sb.AppendLine("FROM	dbo.sysobjects INNER JOIN");
			sb.AppendLine("		dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN");
			sb.AppendLine("		dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype");
			sb.AppendLine("WHERE	(dbo.sysobjects.category = 0) AND");
			sb.AppendLine("		(dbo.sysobjects.xtype = 'P') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'gen_%') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'dt_%') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'sp[_]%diagram%') AND");
			sb.AppendLine("		dbo.systypes.name <> 'sysname' AND");
			sb.AppendLine("		(dbo.sysobjects.uid in (select uid from dbo.sysusers))");
			sb.AppendLine("ORDER BY");
			sb.AppendLine("		dbo.sysobjects.name, dbo.syscolumns.name");
			return sb.ToString();
		}

		internal static string GetSqlForStoredProceduresColumns(StoredProc sp)
		{
			var sb = new StringBuilder();
			System.Windows.Forms.Application.DoEvents();
			sb.AppendLine("SET FMTONLY ON");
			sb.Append("EXEC [" + (string.IsNullOrEmpty(sp.Schema) ? "dbo" : sp.Schema) + "].[" + sp.Name + "] ");

			foreach (var parameter in sp.ParameterList)
			{
				if (parameter.DataType == SqlDbType.UniqueIdentifier)
					sb.Append("@" + parameter.Name + "='540C6D43-5645-40FB-980F-2FF126BFBD5E'");
				else if (parameter.IsTextType())
					sb.Append("@" + parameter.Name + "=''");
				else if (parameter.IsNumericType())
					sb.Append("@" + parameter.Name + "=0");
				else if (parameter.IsBinaryType())
					sb.Append("@" + parameter.Name + "=0x0");
				else if (parameter.DataType == SqlDbType.Bit)
					sb.Append("@" + parameter.Name + "=0");
				else if (parameter.IsDateType())
					sb.Append("@" + parameter.Name + "='2000-01-01'");
				else
					System.Diagnostics.Debug.Write(string.Empty);

				if (sp.ParameterList.IndexOf(parameter) < sp.ParameterList.Count - 1)
					sb.Append(", ");
			}

			sb.AppendLine();
			return sb.ToString();
		}

		internal static string GetSqlForStoredProceduresBody(string schema, string spName, string connectionString)
		{
			var sb = new StringBuilder();
			var ds = DatabaseHelper.ExecuteDataset(connectionString, "sp_helptext '[" + schema + "].[" + spName + "]'");
			if (ds.Tables.Count > 0)
			{
				foreach (DataRow dr in ds.Tables[0].Rows)
				{
					sb.Append(((string)dr[0]).Replace("\r", string.Empty));
				}

				var arr = sb.ToString().Split('\n').ToList();
				sb = new StringBuilder();

				var inBody = false;
				foreach (var lineText in arr)
				{
					var lineText2 = StripComments(lineText);

					//This is FAR from perfect. It assumes the creation line ends with the "AS" keyword for a stored proc
					if (inBody)
					{
						sb.AppendLine(lineText);
					}
					else if (!inBody && (lineText2.ToLower().Trim().EndsWith(" as") || lineText2.ToLower().Trim() == "as"))
					{
						inBody = true;
					}
				}
			}

			return sb.ToString().Trim();
		}

		internal static string GetSqlForStoredProcedures()
		{
			return GetSqlForStoredProcedures(null);
		}

		internal static string GetSqlForStoredProcedures(string name)
		{
			var sb = new StringBuilder();
			sb.AppendLine("SELECT	OBJECT_SCHEMA_NAME(id) as schemaname, dbo.sysobjects.id, dbo.sysobjects.xtype, dbo.sysobjects.name");
			sb.AppendLine("FROM	dbo.sysobjects");
			sb.AppendLine("WHERE (dbo.sysobjects.category = 0) AND");
			sb.AppendLine("		(dbo.sysobjects.xtype = 'P') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'gen_%') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'dt_%') AND");
			sb.AppendLine("		NOT (dbo.sysobjects.name LIKE 'sp[_]%diagram%') AND");

			if (!string.IsNullOrEmpty(name))
			{
				sb.AppendLine("		(dbo.sysobjects.name = '" + name + "') AND");
			}

			sb.AppendLine("		(dbo.sysobjects.uid in (select uid from dbo.sysusers))");
			sb.AppendLine("ORDER BY dbo.sysobjects.name");
			return sb.ToString();
		}

		/// <summary>
		/// Not perfect!! Just strips off what looks like comment
		/// </summary>
		private static string StripComments(string sql)
		{
			if (string.IsNullOrEmpty(sql)) return sql;
			var index = sql.IndexOf("--");
			if (index == -1) return sql;
			return sql.Substring(0, index);
		}
	}
}